const sqlite3 = require('../app/backend/node_modules/sqlite3').verbose();
const db = new sqlite3.Database('../app/backend/data/pph.sqlite');

console.log('🔧 全面修复所有物料的节拍字段分配错误...');
console.log('=====================================');

async function fixAllTaktErrors() {
  return new Promise((resolve, reject) => {
    console.log('📊 开始修复前的统计...');
    
    // 1. 统计修复前的错误情况
    db.get(`SELECT 
              (SELECT COUNT(*) FROM pph_records WHERE is_whole_machine = 0 AND target_takt_time IS NOT NULL) as motor_with_whole_takt,
              (SELECT COUNT(*) FROM pph_records WHERE is_whole_machine = 1 AND target_motor_takt_time IS NOT NULL) as whole_with_motor_takt,
              (SELECT COUNT(*) FROM pph_records WHERE target_takt_time IS NOT NULL AND target_motor_takt_time IS NOT NULL) as double_takt_errors,
              (SELECT COUNT(DISTINCT material_code) FROM pph_records WHERE (is_whole_machine = 0 AND target_takt_time IS NOT NULL) OR (is_whole_machine = 1 AND target_motor_takt_time IS NOT NULL)) as affected_materials
           `, (err, beforeStats) => {
      if (err) {
        console.error('❌ 统计失败:', err);
        return reject(err);
      }
      
      console.log('📈 修复前统计:');
      console.log(`- 电机生产记录错误的整机节拍: ${beforeStats.motor_with_whole_takt} 条`);
      console.log(`- 整机生产记录错误的电机节拍: ${beforeStats.whole_with_motor_takt} 条`);
      console.log(`- 双节拍错误记录: ${beforeStats.double_takt_errors} 条`);
      console.log(`- 受影响的物料数量: ${beforeStats.affected_materials} 个`);
      
      if (beforeStats.motor_with_whole_takt === 0 && beforeStats.whole_with_motor_takt === 0) {
        console.log('✅ 没有发现节拍分配错误，无需修复');
        db.close();
        return resolve({ fixed: false, message: '无需修复' });
      }
      
      console.log('\n🔧 开始执行修复...');
      
      // 2. 修复电机生产记录的错误整机节拍
      console.log('步骤1: 清除电机生产记录的错误整机节拍...');
      db.run(`UPDATE pph_records 
              SET target_takt_time = NULL 
              WHERE is_whole_machine = 0 
              AND target_takt_time IS NOT NULL`, function(err1) {
        if (err1) {
          console.error('❌ 修复电机记录失败:', err1);
          return reject(err1);
        }
        
        const motorFixed = this.changes;
        console.log(`✅ 成功修复 ${motorFixed} 条电机生产记录`);
        
        // 3. 修复整机生产记录的错误电机节拍
        console.log('步骤2: 清除整机生产记录的错误电机节拍...');
        db.run(`UPDATE pph_records 
                SET target_motor_takt_time = NULL 
                WHERE is_whole_machine = 1 
                AND target_motor_takt_time IS NOT NULL`, function(err2) {
          if (err2) {
            console.error('❌ 修复整机记录失败:', err2);
            return reject(err2);
          }
          
          const wholeFixed = this.changes;
          console.log(`✅ 成功修复 ${wholeFixed} 条整机生产记录`);
          
          // 4. 验证修复结果
          console.log('\n🔍 验证修复结果...');
          db.get(`SELECT 
                    (SELECT COUNT(*) FROM pph_records WHERE is_whole_machine = 0 AND target_takt_time IS NOT NULL) as motor_with_whole_takt,
                    (SELECT COUNT(*) FROM pph_records WHERE is_whole_machine = 1 AND target_motor_takt_time IS NOT NULL) as whole_with_motor_takt,
                    (SELECT COUNT(*) FROM pph_records WHERE target_takt_time IS NOT NULL AND target_motor_takt_time IS NOT NULL) as double_takt_errors
                 `, (err3, afterStats) => {
            if (err3) {
              console.error('❌ 验证失败:', err3);
              return reject(err3);
            }
            
            console.log('📈 修复后统计:');
            console.log(`- 电机生产记录错误的整机节拍: ${afterStats.motor_with_whole_takt} 条`);
            console.log(`- 整机生产记录错误的电机节拍: ${afterStats.whole_with_motor_takt} 条`);
            console.log(`- 双节拍错误记录: ${afterStats.double_takt_errors} 条`);
            
            const isFullyFixed = afterStats.motor_with_whole_takt === 0 && 
                               afterStats.whole_with_motor_takt === 0 && 
                               afterStats.double_takt_errors === 0;
            
            if (isFullyFixed) {
              console.log('\n✅ 所有节拍字段分配错误已完全修复！');
              
              // 5. 获取受影响的物料列表
              db.all(`SELECT DISTINCT material_code 
                      FROM pph_records 
                      WHERE (target_takt_time IS NOT NULL OR target_motor_takt_time IS NOT NULL)
                      ORDER BY material_code`, (err4, materials) => {
                if (err4) {
                  console.error('❌ 获取物料列表失败:', err4);
                  return reject(err4);
                }
                
                const materialCodes = materials.map(m => m.material_code);
                
                console.log('\n📋 修复总结:');
                console.log(`- 修复的电机记录: ${motorFixed} 条`);
                console.log(`- 修复的整机记录: ${wholeFixed} 条`);
                console.log(`- 总计修复: ${motorFixed + wholeFixed} 条记录`);
                console.log(`- 涉及物料数量: ${materialCodes.length} 个`);
                
                console.log('\n🚀 下一步建议:');
                console.log('1. 更新服务器代码（git pull + pm2 restart）');
                console.log('2. 执行全量重算，确保所有数据使用正确的逻辑');
                console.log('3. 验证重算结果');
                
                if (materialCodes.length > 0) {
                  console.log('\n💡 重算命令参考:');
                  console.log('物料列表:', JSON.stringify(materialCodes.slice(0, 10))); // 显示前10个作为示例
                  if (materialCodes.length > 10) {
                    console.log(`... 还有 ${materialCodes.length - 10} 个物料`);
                  }
                }
                
                db.close();
                resolve({
                  fixed: true,
                  motorFixed,
                  wholeFixed,
                  totalFixed: motorFixed + wholeFixed,
                  affectedMaterials: materialCodes
                });
              });
            } else {
              console.log('\n❌ 修复不完整，仍有错误记录存在');
              db.close();
              resolve({
                fixed: false,
                message: '修复不完整',
                remainingErrors: afterStats
              });
            }
          });
        });
      });
    });
  });
}

// 执行修复
fixAllTaktErrors().then(result => {
  if (result.fixed) {
    console.log('\n🎉 修复完成！');
  } else {
    console.log('\n⚠️ 修复结果:', result.message);
  }
}).catch(err => {
  console.error('❌ 修复失败:', err);
  process.exit(1);
});
